package com.lsy.mysqlAssistant;

import com.fasterxml.jackson.databind.ObjectMapper;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Tag(name = "mysql")
@RestController
@RequestMapping("/mysql")
public class MysqlController {
    @Autowired
    private MysqlAgent mysqlAgent;



//    private static final String DB_URL = "jdbc:mysql://117.149.210.219:3306/ra_base_analysis?serverTimezone=UTC";
//    private static final String DB_USER = "base_analysis_dify";
//    private static final String DB_PASSWORD = "base_analysis_dify";



//    private static final String DB_URL = "jdbc:mysql://10.102.12.52:3306/dbgpt_company?serverTimezone=UTC";
    private static final String DB_URL = "jdbc:mysql://10.102.12.52:3306/20250821jcxj?serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "rarb66886688";



    @Operation(summary = "对话")
    @PostMapping("/chat")
    public String chat(@RequestBody ChatForm chatForm) {
        if("rm".equals(chatForm.getDbCode())){
            MySqlAgentVo mySqlAgentVo = mysqlAgent.chat(chatForm.getMemoryId(), chatForm.getMessage(), getTableInfo("jdbc:mysql://117.149.210.219:3306/ra_base_analysis?serverTimezone=UTC","base_analysis_dify","base_analysis_dify"));
        }else if("qy".equals(chatForm.getDbCode())){
            MySqlAgentVo mySqlAgentVo = mysqlAgent.chat(chatForm.getMemoryId(), chatForm.getMessage(), getTableInfo("jdbc:mysql://10.102.12.52:3306/dbgpt_company?serverTimezone=UTC","root","rarb66886688"));
            return mySqlAgentVo.getSql();
        }
        else if("jcxj".equals(chatForm.getDbCode())){
            MySqlAgentVo mySqlAgentVo = mysqlAgent.chat(chatForm.getMemoryId(), chatForm.getMessage(), getTableInfo("jdbc:mysql://10.102.12.52:3306/20250821jcxj?serverTimezone=UTC","root","rarb66886688"));

            String sql = mySqlAgentVo.getSql();
            try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
                 Statement statement = connection.createStatement();
                 ResultSet resultSet = statement.executeQuery(sql)) {

                // 处理查询结果
                // 例如，可以将结果转换为 JSON 格式返回给前端
                // 这里仅作示例，实际应用中可能需要更复杂的处理逻辑
                List<Map<String, Object>> resultList = new ArrayList<>();
                int columnCount = resultSet.getMetaData().getColumnCount();

                while (resultSet.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.put(resultSet.getMetaData().getColumnLabel(i), resultSet.getObject(i));
                    }
                    resultList.add(row);
                }

                // 将结果转换为 JSON 字符串格式返回
                ObjectMapper objectMapper = new ObjectMapper();
                return objectMapper.writeValueAsString(resultList);

            } catch (Exception e) {
                e.printStackTrace();
                return "执行SQL时发生错误: " + e.getMessage();
            }

        }
        return "参数错误";

    }


    public static String getTableInfo(String DB_URL,String DB_USER,String DB_PASSWORD){
        String s="";
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // 获取所有表名和表注释
            String tableQuery = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE()";
            try (Statement tableStatement = connection.createStatement();
                 ResultSet tableResultSet = tableStatement.executeQuery(tableQuery)) {
                List<Map<String, Object>> tableList = new ArrayList<>();

                while (tableResultSet.next()) {
                    String tableName = tableResultSet.getString("TABLE_NAME");
                    String tableComment = tableResultSet.getString("TABLE_COMMENT");

                    Map<String, Object> tableInfo = new HashMap<>();
                    tableInfo.put("tableName", tableName);
                    tableInfo.put("tableComment", tableComment);

                    // 获取该表的字段名、字段注释、字段类型和字段约束
                    String columnQuery = "SELECT COLUMN_NAME, COLUMN_COMMENT, DATA_TYPE, COLUMN_KEY " +
                            "FROM INFORMATION_SCHEMA.COLUMNS " +
                            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" + tableName + "'";
                    try (Statement columnStatement = connection.createStatement();
                         ResultSet columnResultSet = columnStatement.executeQuery(columnQuery)) {
                        List<Map<String, String>> columnList = new ArrayList<>();
                        while (columnResultSet.next()) {
                            String columnName = columnResultSet.getString("COLUMN_NAME");
                            String columnComment = columnResultSet.getString("COLUMN_COMMENT");
                            String columnType = columnResultSet.getString("DATA_TYPE");
                            String columnKey = columnResultSet.getString("COLUMN_KEY");

                            Map<String, String> columnInfo = new HashMap<>();
                            columnInfo.put("columnName", columnName);
                            columnInfo.put("columnComment", columnComment);
                            columnInfo.put("columnType", columnType);
                            columnInfo.put("columnKey", columnKey);
                            columnList.add(columnInfo);
                        }
                        tableInfo.put("columns", columnList);
                    }
                    tableList.add(tableInfo);
                }

                // 输出结果
                for (Map<String, Object> table : tableList) {
//                    System.out.println("表名: " + table.get("tableName"));
                    s+="表名: " + table.get("tableName");
                    s+="\n";
//                    System.out.println("表注释: " + table.get("tableComment"));
                    s+="表注释: " + table.get("tableComment");
                    s+="\n";

//                    System.out.println("字段信息:");
                    s+="字段信息:";
                    s+="\n";

                    List<Map<String, String>> columns = (List<Map<String, String>>) table.get("columns");
                    for (Map<String, String> column : columns) {
//                        System.out.println("  字段名: " + column.get("columnName") + ", 字段注释: " + column.get("columnComment") + ", 字段类型: " + column.get("columnType") );
                        s+="  字段名: " + column.get("columnName") + ", 字段注释: " + column.get("columnComment") + ", 字段类型: " + column.get("columnType");
                        s+="\n";

                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(s);
        return s;
    }

    public static void main(String[] args) {
    }


}